"""
基本使用示例
演示如何使用执行引擎执行各种查询
"""

import sys
import os
import time
from typing import List, Dict, Any

# 添加引擎模块到路径
sys.path.insert(0, os.path.join(os.path.dirname(__file__), '..', '..'))

from engine import QueryExecutor
from engine.query_plan import QueryPlan, PlanBuilder
from engine.types import (
    ExecutionContext, OperatorType, JoinType, AggregateFunction,
    SortDirection, IsolationLevel
)
from engine.memory_manager import get_memory_manager
from engine.transaction_manager import get_transaction_manager
from engine.statistics import get_statistics

def print_separator(title: str):
    """打印分隔符"""
    print("\n" + "="*60)
    print(f" {title}")
    print("="*60)

def print_results(results: List[Dict], title: str = "查询结果"):
    """打印查询结果"""
    print(f"\n{title}:")
    print("-" * 40)
    if not results:
        print("(无结果)")
        return
    
    # 打印表头
    if results:
        headers = list(results[0].keys())
        header_line = " | ".join(f"{h:>12}" for h in headers)
        print(header_line)
        print("-" * len(header_line))
        
        # 打印数据行
        for row in results:
            data_line = " | ".join(f"{str(row.get(h, ''))[:12]:>12}" for h in headers)
            print(data_line)
    
    print(f"\n总共 {len(results)} 行")

def example_table_scan():
    """示例1：表扫描"""
    print_separator("示例1：表扫描")
    
    # 创建查询计划构建器
    builder = PlanBuilder()
    
    # 构建简单的表扫描计划
    scan_node = builder.scan_table("users", "u")
    plan = builder.build(scan_node)
    
    print("执行计划:")
    print(plan.explain())
    
    # 执行查询
    executor = QueryExecutor()
    context = ExecutionContext()
    
    results = []
    try:
        for batch in executor.execute_plan(plan, context):
            results.extend(batch)
        
        print_results(results, "用户表扫描结果")
        
    except Exception as e:
        print(f"执行出错: {e}")

def example_filter_and_project():
    """示例2：过滤和投影"""
    print_separator("示例2：过滤和投影")
    
    builder = PlanBuilder()
    
    # 构建计划：扫描 -> 过滤 -> 投影
    scan_node = builder.scan_table("users", "u")
    filter_node = builder.filter(scan_node, "age > 25")
    project_node = builder.project(filter_node, ["name", "age", "city"])
    
    plan = builder.build(project_node)
    
    print("执行计划:")
    print(plan.explain())
    
    # 执行查询
    executor = QueryExecutor()
    context = ExecutionContext()
    
    results = []
    try:
        for batch in executor.execute_plan(plan, context):
            results.extend(batch)
        
        print_results(results, "过滤后的用户信息")
        
    except Exception as e:
        print(f"执行出错: {e}")

def example_join():
    """示例3：连接查询"""
    print_separator("示例3：连接查询")
    
    builder = PlanBuilder()
    
    # 构建连接计划
    users_scan = builder.scan_table("users", "u")
    orders_scan = builder.scan_table("orders", "o")
    
    # 内连接：用户ID匹配
    join_node = builder.join(
        users_scan, orders_scan,
        JoinType.INNER, "u.id = o.user_id"
    )
    
    # 投影结果
    project_node = builder.project(join_node, [
        "left.name", "left.city", "right.order_id", "right.amount"
    ])
    
    plan = builder.build(project_node)
    
    print("执行计划:")
    print(plan.explain())
    
    # 执行查询
    executor = QueryExecutor()
    context = ExecutionContext()
    
    results = []
    try:
        for batch in executor.execute_plan(plan, context):
            results.extend(batch)
        
        print_results(results, "用户订单连接结果")
        
    except Exception as e:
        print(f"执行出错: {e}")

def example_aggregation():
    """示例4：聚合查询"""
    print_separator("示例4：聚合查询")
    
    builder = PlanBuilder()
    
    # 构建聚合计划
    scan_node = builder.scan_table("orders", "o")
    
    # 按用户ID分组，计算订单数量和总金额
    agg_specs = [
        {"function": "COUNT", "column": "*", "alias": "order_count"},
        {"function": "SUM", "column": "amount", "alias": "total_amount"},
        {"function": "AVG", "column": "amount", "alias": "avg_amount"}
    ]
    
    agg_node = builder.aggregate(scan_node, ["user_id"], agg_specs)
    
    plan = builder.build(agg_node)
    
    print("执行计划:")
    print(plan.explain())
    
    # 执行查询
    executor = QueryExecutor()
    context = ExecutionContext()
    
    results = []
    try:
        for batch in executor.execute_plan(plan, context):
            results.extend(batch)
        
        print_results(results, "用户订单聚合结果")
        
    except Exception as e:
        print(f"执行出错: {e}")

def example_sorting_and_limit():
    """示例5：排序和限制"""
    print_separator("示例5：排序和限制")
    
    builder = PlanBuilder()
    
    # 构建排序限制计划
    scan_node = builder.scan_table("users", "u")
    
    # 按年龄降序排序
    sort_specs = [
        {"column": "age", "direction": "DESC"},
        {"column": "name", "direction": "ASC"}
    ]
    sort_node = builder.sort(scan_node, sort_specs)
    
    # 限制前3条记录
    limit_node = builder.limit(sort_node, 3, 0)
    
    plan = builder.build(limit_node)
    
    print("执行计划:")
    print(plan.explain())
    
    # 执行查询
    executor = QueryExecutor()
    context = ExecutionContext()
    
    results = []
    try:
        for batch in executor.execute_plan(plan, context):
            results.extend(batch)
        
        print_results(results, "年龄最大的3个用户")
        
    except Exception as e:
        print(f"执行出错: {e}")

def example_complex_query():
    """示例6：复杂查询"""
    print_separator("示例6：复杂查询")
    
    builder = PlanBuilder()
    
    # 构建复杂查询：连接 + 过滤 + 聚合 + 排序 + 限制
    
    # 1. 扫描用户表
    users_scan = builder.scan_table("users", "u")
    users_filter = builder.filter(users_scan, "city in ('北京', '上海')")
    
    # 2. 扫描订单表
    orders_scan = builder.scan_table("orders", "o")
    orders_filter = builder.filter(orders_scan, "status = 'completed'")
    
    # 3. 连接用户和订单
    join_node = builder.join(
        users_filter, orders_filter,
        JoinType.INNER, "u.id = o.user_id"
    )
    
    # 4. 投影需要的字段
    project_node = builder.project(join_node, [
        "left.name", "left.city", "right.amount"
    ])
    
    # 5. 按城市聚合
    agg_specs = [
        {"function": "COUNT", "column": "*", "alias": "user_count"},
        {"function": "SUM", "column": "right.amount", "alias": "total_sales"},
        {"function": "AVG", "column": "right.amount", "alias": "avg_order_value"}
    ]
    agg_node = builder.aggregate(project_node, ["left.city"], agg_specs)
    
    # 6. 按总销售额降序排序
    sort_specs = [{"column": "total_sales", "direction": "DESC"}]
    sort_node = builder.sort(agg_node, sort_specs)
    
    plan = builder.build(sort_node)
    
    print("执行计划:")
    print(plan.explain())
    
    # 执行查询
    executor = QueryExecutor()
    context = ExecutionContext(timeout=30)  # 30秒超时
    
    results = []
    try:
        for batch in executor.execute_plan(plan, context):
            results.extend(batch)
        
        print_results(results, "各城市销售统计")
        
    except Exception as e:
        print(f"执行出错: {e}")

def example_transaction():
    """示例7：事务处理"""
    print_separator("示例7：事务处理")
    
    tx_manager = get_transaction_manager()
    
    # 开始事务
    tx_id = tx_manager.begin_transaction(IsolationLevel.READ_COMMITTED)
    print(f"开始事务: {tx_id}")
    
    try:
        # 在事务中执行查询
        builder = PlanBuilder()
        scan_node = builder.scan_table("users", "u")
        filter_node = builder.filter(scan_node, "age >= 30")
        plan = builder.build(filter_node)
        
        executor = QueryExecutor()
        context = ExecutionContext(transaction_id=tx_id)
        
        results = []
        for batch in executor.execute_plan(plan, context):
            results.extend(batch)
        
        print_results(results, "事务中的查询结果")
        
        # 提交事务
        if tx_manager.commit_transaction(tx_id):
            print(f"事务 {tx_id} 提交成功")
        else:
            print(f"事务 {tx_id} 提交失败")
            
    except Exception as e:
        # 回滚事务
        print(f"执行出错: {e}")
        if tx_manager.abort_transaction(tx_id):
            print(f"事务 {tx_id} 已回滚")

def example_performance_monitoring():
    """示例8：性能监控"""
    print_separator("示例8：性能监控")
    
    executor = QueryExecutor()
    
    # 执行几个查询
    print("执行多个查询进行性能测试...")
    
    for i in range(3):
        builder = PlanBuilder()
        scan_node = builder.scan_table("users", "u")
        filter_node = builder.filter(scan_node, f"age > {20 + i * 5}")
        plan = builder.build(filter_node)
        
        context = ExecutionContext()
        
        results = []
        for batch in executor.execute_plan(plan, context):
            results.extend(batch)
        
        print(f"查询 {i+1} 完成，返回 {len(results)} 条记录")
        time.sleep(0.1)  # 模拟间隔
    
    # 获取性能指标
    metrics = executor.get_performance_metrics()
    
    print("\n性能摘要:")
    summary = metrics['summary']
    for key, value in summary.items():
        print(f"  {key}: {value}")
    
    print("\n慢查询 (Top 3):")
    slow_queries = metrics['slow_queries'][:3]
    for i, query in enumerate(slow_queries, 1):
        print(f"  {i}. 查询ID: {query['query_id'][:8]}...")
        print(f"     执行时间: {query['duration_ms']:.2f}ms")
        print(f"     处理行数: {query['rows_processed']}")
    
    print("\n操作符性能:")
    op_perf = metrics['operator_performance']
    for op_type, stats in list(op_perf.items())[:5]:  # 显示前5个
        print(f"  {op_type}:")
        print(f"    总执行次数: {stats['total_executions']}")
        print(f"    平均执行时间: {stats['avg_time_ms']:.2f}ms")
        print(f"    平均选择性: {stats['avg_selectivity']:.3f}")

def example_memory_management():
    """示例9：内存管理"""
    print_separator("示例9：内存管理")
    
    mem_manager = get_memory_manager()
    
    print("内存管理器状态:")
    stats = mem_manager.get_memory_stats()
    
    print(f"最大内存限制: {stats['max_memory_bytes'] / (1024*1024):.1f} MB")
    print(f"当前已分配: {stats['total_allocated_bytes'] / (1024*1024):.1f} MB")
    
    print("\n内存池状态:")
    for pool_name, pool_stats in stats['pools'].items():
        print(f"  {pool_name}池:")
        print(f"    池大小: {pool_stats['pool_size'] / (1024*1024):.1f} MB")
        print(f"    已分配: {pool_stats['allocated_bytes'] / (1024*1024):.1f} MB")
        print(f"    利用率: {pool_stats['utilization']:.1%}")
    
    print(f"\n系统内存:")
    sys_mem = stats['system_memory']
    print(f"  物理内存: {sys_mem['rss_bytes'] / (1024*1024):.1f} MB")
    print(f"  虚拟内存: {sys_mem['vms_bytes'] / (1024*1024):.1f} MB")
    print(f"  内存占用率: {sys_mem['percent']:.1f}%")

def main():
    """主函数"""
    print("数据库执行引擎基本使用示例")
    print("=" * 60)
    
    try:
        # 运行各种示例
        example_table_scan()
        example_filter_and_project()
        example_join()
        example_aggregation()
        example_sorting_and_limit()
        example_complex_query()
        example_transaction()
        example_performance_monitoring()
        example_memory_management()
        
        print_separator("所有示例执行完成")
        
        # 获取最终统计
        stats = get_statistics()
        final_summary = stats.get_performance_summary()
        
        print(f"总查询数: {final_summary['total_queries']}")
        print(f"成功查询: {final_summary['completed_queries']}")
        print(f"失败查询: {final_summary['failed_queries']}")
        print(f"平均查询时间: {final_summary['avg_query_duration_ms']:.2f}ms")
        print(f"总处理行数: {final_summary['total_rows_processed']}")
        print(f"总返回行数: {final_summary['total_rows_returned']}")
        
    except KeyboardInterrupt:
        print("\n\n用户中断执行")
    except Exception as e:
        print(f"\n\n执行出错: {e}")
        import traceback
        traceback.print_exc()
    finally:
        # 清理资源
        print("\n清理资源...")
        executor = QueryExecutor()
        executor.shutdown()
        print("清理完成")

if __name__ == "__main__":
    main()


